from random import randint
from uuid import uuid4

from sqlalchemy import Column, Integer, String, Float, Text, and_, or_

from db_util import Base, Session,engine


class Article(Base):
    __tablename__ = 't_article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    price = Column(Float, nullable=False)
    content = Column(Text)

    def __repr__(self):
        return f"<Article(title:{self.title} price:{self.price} content:{self.content})>"


def create_data():
    with Session() as ses:
        for i in range(10):
            if i % 2 == 0:
                art = Article(title=f'title{i + 1}', price=randint(1, 100), content=uuid4())
            else:
                art = Article(title=f'TITLE{i + 1}', price=randint(1, 100))
            ses.add(art)
        ses.commit()

def query_data():
    with Session() as session:
        # rs = session.query(Article).filter_by(id=1).first()
        rs = session.query(Article).filter(Article.id == 1).first()
        print(rs)

def query_data_equal():
    with Session() as session:
        rs = session.query(Article).filter(Article.title == 'title2').first()
        print(rs)

def query_data_not_equal():
    with Session() as session:
        rs = session.query(Article).filter(Article.title != 'title2').first()
        print(rs)

def query_data_like():
    with Session() as session:
        rs = session.query(Article).filter(Article.title.like('title%')).first()
        print(rs)

def query_data_in():
    with Session() as session:
        rs = session.query(Article).filter(Article.title.in_(['title1','title3','title6'])).all()
        print(rs)

def query_data_not_in():
    with Session() as session:
        rs = session.query(Article).filter(~Article.title.in_(['title1','title3','title6'])).all()
        print(rs)

def query_data_null():
    with Session() as session:
        rs = session.query(Article).filter(Article.content == None).all()
        print(rs)

def query_data_not_null():
    with Session() as session:
        rs = session.query(Article).filter(Article.content != None).all()
        print(rs)

def query_data_and():
    with Session() as ses:
        # rs = ses.query(Article).filter(Article.title != 'title4' and Article.price > 50).all()
        # rs = ses.query(Article).filter(Article.title != 'title4' , Article.price > 50).all()
        rs = ses.query(Article).filter(and_(Article.title != 'title4' , Article.price > 50)).all()
        print(rs)

def query_data_or():
    with Session() as ses:
        rs = ses.query(Article).filter(or_(Article.title != 'title4' , Article.price > 50)).all()
        print(rs)

if __name__ == '__main__':
    # Base.metadata.create_all(engine)
    # create_data()
    # query_data()
    # query_data_equal()
    # query_data_not_equal()
    # query_data_like()
    # query_data_in()
    # query_data_not_in()
    # query_data_null()
    # query_data_not_null()
    # query_data_and()
    query_data_or()